﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_device_sel_hierall]
	@DEVICE_ID int
AS

DECLARE @TMP_HEIRALL TABLE ([DEVICE_ID] INT, [DEVICE_ASSET_NAME] VARCHAR(100), [PARENT_ID] INT, [DEVICE_TYPE] INT, [DEVICE_TYPE_NAME] VARCHAR(100))

BEGIN
	 -- WALK UP THE TREE.
	 WITH nested_Up AS
	(
		SELECT A.[DEVICE_ID], A.[DEVICE_ASSET_NAME], A.[PARENT_ID], B.[DEVICE_TYPE], B.[DEVICE_TYPE_NAME]
		  FROM flcr.[DEVICE] A
		 INNER JOIN flcr.[DEVICE_TYPE] B
		    ON A.[DEVICE_TYPE] = B.[DEVICE_TYPE] 
		WHERE A.[DEVICE_ID] = @DEVICE_ID

		UNION ALL

		SELECT B.[DEVICE_ID], B.[DEVICE_ASSET_NAME], B.[PARENT_ID], B.[DEVICE_TYPE], C.[DEVICE_TYPE_NAME] 
		  FROM nested_Up a
		 INNER JOIN flcr.[DEVICE] B
			ON A.[PARENT_ID] = B.[DEVICE_ID]
		 INNER JOIN flcr.[DEVICE_TYPE] C
		    ON B.[DEVICE_TYPE] = C.[DEVICE_TYPE] 	
	)
	INSERT INTO @TMP_HEIRALL 
	SELECT * FROM nested_Up;
	
	-- WALK DOWN THE TREE.
	 WITH nested_Down AS
	(
		SELECT A.[DEVICE_ID], A.[DEVICE_ASSET_NAME], A.[PARENT_ID], B.[DEVICE_TYPE], B.[DEVICE_TYPE_NAME]
		  FROM flcr.[DEVICE] A
		 INNER JOIN flcr.[DEVICE_TYPE] B
		    ON A.[DEVICE_TYPE] = B.[DEVICE_TYPE] 
		WHERE A.[DEVICE_ID] = @DEVICE_ID

		UNION ALL

		SELECT B.[DEVICE_ID], B.[DEVICE_ASSET_NAME], B.[PARENT_ID], B.[DEVICE_TYPE], C.[DEVICE_TYPE_NAME]
		  FROM nested_Down A
		 INNER JOIN flcr.[DEVICE] B
			ON A.[DEVICE_ID] = B.[PARENT_ID]
		 INNER JOIN flcr.[DEVICE_TYPE] C
		    ON B.[DEVICE_TYPE] = C.[DEVICE_TYPE] 		
	)
	INSERT INTO @TMP_HEIRALL 
	SELECT * FROM nested_Down;
	
	-- RETURN FULL HEIRARCHY
	SELECT DISTINCT * FROM @TMP_HEIRALL
	 ORDER BY [PARENT_ID]
	
END